package dao.impl;

import dao.UserDao;
import domain.Article;
import domain.Favour;
import domain.User;
import util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import util.Md5Util;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public User findByUsername(String username) {
        User user = null;

        //异常处理,如果User封装失败,肯定还会抛出异常,进行了异常处理抛出异常不会崩溃
        try {
            //1.定义sql
            String sql = "select * from tab_user where username = ?";
            //2.执行sql
            user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username);
        } catch (Exception e) {

        }
        return user;
    }

    @Override
    public void save(User user, String createTime) {
        //1.定义sql
        String sql = "insert into tab_user(username,password,nickname,birthday,sex,telephone,email,status,code,createTime,modifyTime,image) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        //2.执行sql

        try {
            template.update(
                    sql,
                    user.getUsername(),
                    Md5Util.encodeByMd5(user.getPassword()),
                    user.getNickname(),
                    user.getBirthday(),
                    user.getSex(),
                    user.getTelephone(),
                    user.getEmail(),
                    user.getStatus(),
                    user.getCode(),
                    createTime,
                    createTime,
                    "images/photo_1.jpg"
                    );
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据激活码查询用户对象
     * @param code
     * @return
     */
    @Override
    public User findByCode(String code) {
        User user = null;
        try {
            String sql = "select * from tab_user where code = ?";
            user = template.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),code);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 修改指定用户激活状态
     * @param user
     */
    @Override
    public void updateStatus(User user) {
        String sql = " update tab_user set status = 'Y' where uid=?";
        template.update(sql,user.getUid());
    }

    /**
     * 根据用户名和密码查询的方法
     * @param username
     * @param password
     * @return
     */
    @Override
    public User findByUsernameAndPassword(String username, String password) {
        User user = null;
        try {
            //1.定义sql
            String sql = "select * from tab_user where username = ? and password = ?";
            //2.执行sql
            user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username,Md5Util.encodeByMd5(password));
        } catch (Exception e) {

        }
        return user;
    }

    @Override
    public int findArticleNum(String code) {
        User user = null;
        String sql = "select count(code) from tab_article where code = ? and recycle = ?";
        return  template.queryForObject(sql, Integer.class, code, "N");
    }

    @Override
    public int updateNickNameByCode(String code, String nickname) {
        String sql = "update tab_user set username = ? where code = ?";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setString(1, nickname);
            statement.setString(2, code);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int updatePasswordByCode(String code, String password) {
        String sql = "update tab_user set password = ? where code = ?";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setString(1, password);
            statement.setString(2, code);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int setArticle(int cid, String visible, String title, String text, String createTime, String code) {
        String sql =
                "insert into tab_article " +
                "(cid, visible, title, text,createTime,modifyTime,browserNum,favour,recycle,code)" +
                "values (?, ?, ?, ?, ?, ?, ?, ?, 'N', ?)";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setInt(1, cid);
            statement.setString(2, String.valueOf(visible.charAt(0)));
            statement.setString(3, title);
            statement.setString(4, text);
            statement.setString(5, createTime);
            statement.setString(6, createTime);
            statement.setInt(7, 0);
            statement.setInt(8, 0);
            statement.setString(9, code);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Article findArticleByCode(String code, String createTime) {
        Article article = null;
        String sql = "select * from tab_article where code = ? and createTime = ?";
        article = template.queryForObject(sql,new BeanPropertyRowMapper<Article>(Article.class),code,createTime);
        return article;
    }

    @Override
    public int setLabelByCode(String code, int rid, int tag) {
        String sql = "insert into tab_article_label(rid,lid,code)" +
                "values(?,?,?) ";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setInt(1, rid);
            statement.setInt(2, tag);
            statement.setString(3,code);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int findTotalCount(String code) {
        //1.定义模版sql
        String sql = "select count(*) from tab_article where code = ? and recycle = ?";
        return template.queryForObject(
                sql,
                Integer.class, //java 1.5特性会把返回的Integer类型的数据自动拆箱为int类型
                code,
                "N"
        );
    }

    @Override
    public List<Article> findByPage(int start, int rows, String code) {
        String sql = "select * from tab_article where code = ? and recycle='N' limit ?,?";
        return template.query(
                sql,
                new BeanPropertyRowMapper<Article>(Article.class),
                code,
                start,
                rows
        );
    }

    @Override
    public void deleteOneArticle(int rid) {
        String sql = "update tab_article set recycle = ? where rid = ?";
        template.update(sql, "Y",rid);
    }

    @Override
    public Article findArticleByRid(int rid) {
        String sql = "select * from tab_article where rid = ?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Article>(Article.class),rid);
    }

    @Override
    public void updateArticle(int rid, String title, String text, String modifyTime, String visible) {
        String sql = "UPDATE tab_article SET title = ?, text = ?, modifyTime = ?, visible = ? WHERE rid = ?";
        template.update(sql,title,text,modifyTime,visible,rid);
    }

    @Override
    public void deleteArticleFromRecycle(int rid) {
        String sql = "delete from tab_article where rid = ?";
        template.update(sql, rid);
    }

    @Override
    public int findTotalCountFromRecycle(String code) {
        //1.定义模版sql
        String sql = "select count(*) from tab_article where code = ? and recycle LIKE ?";
        return template.queryForObject(sql, Integer.class, code, "%Y%");
    }

    @Override
    public List<Article> findByPageFromRecycle(int start, int rows, String code) {
        String sql = "select * from tab_article where code = ? and recycle LIKE ? limit ?,?";
        return template.query(
                sql,
                new BeanPropertyRowMapper<Article>(Article.class),
                code,
                "%Y%%",
                start,
                rows
        );
    }

    @Override
    public Favour findLikeOrNot(String code, int rid) {
        Favour favour = null;
        try {
            String sql = "select *from tab_article_favour where code = ? and rid = ?";
            favour =  template.queryForObject(sql,new BeanPropertyRowMapper<Favour>(Favour.class),code,rid);
        } catch (DataAccessException e) {
            System.out.println("这篇文章未被点赞,rid:" + rid);
            return null;
        }
        return favour;
    }

    @Override
    public void addFavour(int rid) {
        String sql = "UPDATE tab_article SET favour = favour + 1 WHERE rid = ?";
        template.update(sql, rid);
    }

    @Override
    public int conformLike(String code, int rid) {
        String sql = "INSERT INTO tab_article_favour (rid,code) VALUES (?,?)";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setInt(1, rid);
            statement.setString(2, code);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void updateImageByCode(String code, String image) {
        String sql = "UPDATE tab_user SET image = ? WHERE code = ?";
        template.update(sql, image, code);
    }

    @Override
    public void recoverArticleByRid(int rid) {
        String sql = "UPDATE tab_article SET recycle = ? WHERE rid = ?";
        template.update(sql, "N", rid);
    }

}
